﻿using System;
using System.Collections;
using System.Data;
using Telerik.Web.UI;
using System.Web.UI;
using KPIS.GERP.GAMS.DAL;
using KPIS.GERP.GAMS.APP.App_UI.Commons;
using System.Collections.Generic;

namespace KPIS.GERP.GAMS.APP.App_UI.Forms
{
    public partial class Reports2 : System.Web.UI.Page
    {
        Commons.Main main = new Commons.Main();
        D_ACM_MAS_FISCAL_Period d_acm_fiscal_period = new D_ACM_MAS_FISCAL_Period();
        D_BGM_MAS_Source source = new D_BGM_MAS_Source();
        D_ACM_GENERAL_Ledger lg = new D_ACM_GENERAL_Ledger();
        MailMerge mailMerge = new MailMerge();

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                hReportName.Text = Request.QueryString["ReportName"];
                txtBgmYear.Text = source.GetCurrentBgmYear();

                switch (hReportName.Text)
                {
                    case "IncomeStatements":
                        chkIncludeClosePeriodAdjust.Visible = false;
                        break;
                    case "BalanceSheet":
                        chkIncludeClosePeriodAdjust.Visible = false;
                        break;
                    case "TrialBalance":
                        chkIncludeClosePeriodAdjust.Visible = true;
                        break;
                }

                rblCriteria_SelectedIndexChanged(null, null);
                LoadGrdBgmSource();
            }
        }

        protected void LoadGrdBgmSource()
        {
            grdBgmSource.DataSource = source.GetList();
            grdBgmSource.DataBind();
        }

        protected void btnReport_Click(object sender, EventArgs e)
        {
            string strBgmSourceSeq = "";
            string strBgmYear = txtBgmYear.Text;
            DateTime? dtStartDate = dtpStartDate.GetDateValue();
            DateTime? dtEndDate = dtpEndDate.GetDateValue();

            if (grdBgmSource.SelectedItems.Count == 0)
            {
                lblWarning.Visible = true;
                return;
            }
            else
            {
                for (int i = 0; i < grdBgmSource.SelectedItems.Count; i++)
                {
                    GridDataItem item = (GridDataItem)grdBgmSource.SelectedItems[i];
                    strBgmSourceSeq += item["bgm_source_seq"].Text + (i == grdBgmSource.SelectedItems.Count - 1 ? "" : ",");
                }
            }

            switch (rblCriteria.SelectedValue)
            {
                case "date":
                    if (dtStartDate == null || dtEndDate == null)
                    {
                        lblWarning.Visible = true;
                        return;
                    }
                    break;
                case "year":
                    if (string.IsNullOrEmpty(txtBgmYear.Text))
                    {
                        lblWarning.Visible = true;
                        return;
                    }

                    int nBgmYear = Convert.ToInt32(strBgmYear);

                    dtStartDate = new DateTime(nBgmYear - 544, 10, 1);
                    dtEndDate = new DateTime(nBgmYear - 543, 9, 30);
                    break;
                case "quarter":
                    if (string.IsNullOrEmpty(txtBgmYear.Text))
                    {
                        lblWarning.Visible = true;
                        return;
                    }

                    int nBgmYear2 = Convert.ToInt32(strBgmYear);

                    switch (rblQuarter.SelectedValue)
                    {
                        case "1":
                            dtStartDate = new DateTime(nBgmYear2 - 544, 10, 1);
                            dtEndDate = new DateTime(nBgmYear2 - 544, 12, 31);
                        break;
                        case "2":
                            dtStartDate = new DateTime(nBgmYear2 - 543, 1, 1);
                            dtEndDate = new DateTime(nBgmYear2 - 543, 3, 31);
                        break;
                        case "3":
                            dtStartDate = new DateTime(nBgmYear2 - 543, 4, 1);
                            dtEndDate = new DateTime(nBgmYear2 - 543, 6, 30);
                        break;
                        case "4":
                            dtStartDate = new DateTime(nBgmYear2 - 543, 7, 1);
                            dtEndDate = new DateTime(nBgmYear2 - 543, 9, 30);
                        break;
                        default:
                            dtStartDate = new DateTime(nBgmYear2 - 544, 10, 1);
                            dtEndDate = new DateTime(nBgmYear2 - 543, 9, 30);
                        break;
                    }
                    break;
            }

            switch (hReportName.Text)
            {
                case "IncomeStatements":
                    IncomeStatements(strBgmSourceSeq, dtStartDate.Value, dtEndDate.Value);
                    break;
                case "BalanceSheet":
                    BalanceSheet(strBgmSourceSeq, dtStartDate.Value, dtEndDate.Value);
                    break;
                case "TrialBalance":
                    TrialBalance(strBgmSourceSeq, dtStartDate.Value, dtEndDate.Value);
                    break;
            }
        }

        protected void rblCriteria_SelectedIndexChanged(object sender, EventArgs e)
        {
            switch (rblCriteria.SelectedValue)
            {
                case "date":
                    tblFilterDate.Visible = true;
                    tblFilterYear.Visible = false;
                    tblFilterQuarter.Visible = false;
                    break;
                case "year":
                    tblFilterDate.Visible = false;
                    tblFilterYear.Visible = true;
                    tblFilterQuarter.Visible = false;
                    break;
                case "quarter":
                    tblFilterDate.Visible = false;
                    tblFilterYear.Visible = true;
                    tblFilterQuarter.Visible = true;
                    break;
            }
        }

        #region report
        private void BalanceSheet(string strBgmSourceSeq, DateTime dtStartDate, DateTime dtEndDate)
        {
            DataTable dtb = lg.ReportBalanceSheet(strBgmSourceSeq, dtStartDate, dtEndDate, "'1', '2', '3'", null, chkPost.Checked);
            Dictionary<string, string> dic = new Dictionary<string, string>();

            int nRow = 4;
            double dSum1 = 0;
            double dSum2 = 0;

            switch (rblCriteria.SelectedValue)
            {
                case "date":
                    dic.Add("A1", "ตั้งแต่วันที่ " + dtStartDate.ToLongDateString() + " ถึงวันที่ " + dtEndDate.ToLongDateString());
                    break;
                case "year":
                    dic.Add("A1", "ประจำปี " + (dtEndDate.Year + 543).ToString());
                    break;
                case "quarter":
                    if (rblQuarter.SelectedValue == "4")
                    {
                        dic.Add("A1", "ตั้งแต่วันที่ " + dtStartDate.ToLongDateString() + " ถึงวันที่ " + dtEndDate.ToLongDateString());
                    }
                    break;
            }

            for (int i = 0; i < dtb.Rows.Count; i++)
            {
                DataRow dr = dtb.Rows[i];

                string strAccountGroup = dr["account_group"].ToString();
                string strAccountCode = dr["account_code"].ToString();
                string strAccountCodeGFMIS = dr["gfmis_code"].ToString();
                string strAccountName = dr["account_name"].ToString();
                double dAmount1 = (!string.IsNullOrEmpty(dr["amount1"].ToString()) ? Convert.ToDouble(dr["amount1"].ToString()) : 0);
                double dAmount2 = (!string.IsNullOrEmpty(dr["amount2"].ToString()) ? Convert.ToDouble(dr["amount2"].ToString()) : 0);
                double dAmount3 = dAmount1 + dAmount2;

                if (i == 0)
                {
                    switch (strAccountGroup)
                    {
                        case "1":
                            dic.Add("A2", "สินทรัพย์");
                            break;
                        case "2":
                            dic.Add("A2", "หนี้สินและส่วนของผู้เป็นหุ้นส่วน");
                            break;
                        case "3":
                            dic.Add("A2", "ส่วนของผู้ถือหุ้น");
                            break;
                    }
                    dic.Add("A3", "รหัสบัญชี");
                    dic.Add("B3", "รหัสบัญชี GFMIS");
                    dic.Add("C3", "รายการบัญชี");
                    dic.Add("D3", "เงินงบประมาณ");
                    dic.Add("E3", "เงินรายได้");
                    dic.Add("F3", "รวม");
                }

                dic.Add("A" + nRow.ToString(), strAccountCode);
                dic.Add("B" + nRow.ToString(), strAccountCodeGFMIS);
                dic.Add("C" + nRow.ToString(), strAccountName);
                dic.Add("D" + nRow.ToString(), dAmount1.ToString("#,##0.00"));
                dic.Add("E" + nRow.ToString(), dAmount2.ToString("#,##0.00"));
                dic.Add("F" + nRow.ToString(), dAmount3.ToString("#,##0.00"));

                dSum1 += dAmount1;
                dSum2 += dAmount2;

                nRow++;

                if (i == dtb.Rows.Count - 1 || dtb.Rows[i + 1]["account_group"].ToString() != strAccountGroup)
                {
                    switch (strAccountGroup)
                    {
                        case "1":
                            dic.Add("A" + nRow.ToString(), "รวมสินทรัพย์");
                            break;
                        case "2":
                            dic.Add("A" + nRow.ToString(), "รวมหนี้สินและส่วนของผู้เป็นหุ้นส่วน");
                            break;
                        case "3":
                            dic.Add("A" + nRow.ToString(), "รวมส่วนของผู้ถือหุ้น");
                            break;
                    }

                    dic.Add("D" + nRow.ToString(), dSum1.ToString("#,##0.00"));
                    dic.Add("E" + nRow.ToString(), dSum2.ToString("#,##0.00"));
                    dic.Add("F" + nRow.ToString(), (dSum1 + dSum2).ToString("#,##0.00"));
                    dSum1 = 0;
                    dSum2 = 0;
                    nRow++;
                    nRow++;

                    if (i != dtb.Rows.Count - 1)
                    {
                        switch (dtb.Rows[i + 1]["account_group"].ToString())
                        {
                            case "1":
                                dic.Add("A" + nRow.ToString(), "สินทรัพย์");
                                break;
                            case "2":
                                dic.Add("A" + nRow.ToString(), "หนี้สินและส่วนของผู้เป็นหุ้นส่วน");
                                break;
                            case "3":
                                dic.Add("A" + nRow.ToString(), "ส่วนของผู้ถือหุ้น");
                                break;
                        }
                        nRow++;
                        dic.Add("A" + nRow.ToString(), "รหัสบัญชี");
                        dic.Add("B" + nRow.ToString(), "รหัสบัญชี GFMIS");
                        dic.Add("C" + nRow.ToString(), "รายการบัญชี");
                        dic.Add("D" + nRow.ToString(), "เงินงบประมาณ");
                        dic.Add("E" + nRow.ToString(), "เงินรายได้");
                        dic.Add("F" + nRow.ToString(), "รวม");
                        nRow++;
                    }
                    else
                    {
                        nRow++;
                        dic.Add("A" + nRow.ToString(), "รวมรายได้ - ค่าใช้จ่าย");

                        DataTable dtb2 = lg.IncomeDiffOutcome(strBgmSourceSeq, dtStartDate, dtEndDate, chkPost.Checked);

                        double dTotal = 0;
                        foreach (DataRow dr2 in dtb2.Rows)
                        {
                            if (dr2["bgm_source_seq"].ToString() == "1")
                            {
                                dic.Add("D" + nRow.ToString(), (main.ConvertNullDouble(dr2["amount"]) ?? 0).ToString("#,##0.00"));

                                dTotal += main.ConvertNullDouble(dr2["amount"]) ?? 0;
                            }

                            if (dr2["bgm_source_seq"].ToString() == "11")
                            {
                                dic.Add("E" + nRow.ToString(), (main.ConvertNullDouble(dr2["amount"]) ?? 0).ToString("#,##0.00"));

                                dTotal += main.ConvertNullDouble(dr2["amount"]) ?? 0;
                            }
                        }

                        dic.Add("F" + nRow.ToString(), dTotal.ToString("#,##0.00"));
                        //var dSum = dSumIncome - dSumOutcome;

                        //dic.Add("D" + nRow.ToString(), dSum >= 0 ? dSum.ToString("#,##0.00") : (-dSum).ToString("(#,##0.00)"));
                    }
                }
            }

            mailMerge.MergeExcel(Response, Server.MapPath("~/Reports/Templates/งบแสดงฐานะการเงิน.xlsx"), string.Format("งบแสดงฐานะการเงิน-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), null, dic);
        }

        private void IncomeStatements(string strBgmSourceSeq, DateTime dtStartDate, DateTime dtEndDate)
        {
            DataTable dtb = lg.ReportIncomeStatments(strBgmSourceSeq, dtStartDate, dtEndDate, "'4', '5'", null, chkPost.Checked);
            Dictionary<string, string> dic = new Dictionary<string, string>();

            int nRow = 3;
            double dSumIncome = 0;
            double dSumOutcome = 0;

            switch (rblCriteria.SelectedValue)
            {
                case "date":
                    dic.Add("A1", "ตั้งแต่วันที่ " + dtStartDate.ToLongDateString() + " ถึงวันที่ " + dtEndDate.ToLongDateString());
                    break;
                case "year":
                    dic.Add("A1", "ประจำปี " + (dtEndDate.Year + 543).ToString());
                    break;
                case "quarter":
                    if (rblQuarter.SelectedValue == "4")
                    {
                        dic.Add("A1", "ตั้งแต่วันที่ " + dtStartDate.ToLongDateString() + " ถึงวันที่ " + dtEndDate.ToLongDateString());
                    }
                    break;
            }

            for (int i = 0; i < dtb.Rows.Count; i++)
            {
                DataRow dr = dtb.Rows[i];

                string strAccountGroup = dr["account_group"].ToString();
                string strAccountCode = dr["account_code"].ToString();
                string strAccountCodeGFMIS = dr["gfmis_code"].ToString();
                string strAccountName = dr["account_name"].ToString();
                double dAmount = (!string.IsNullOrEmpty(dr["amount"].ToString()) ? Convert.ToDouble(dr["amount"].ToString()) : 0);

                if (i == 0)
                {
                    dic.Add("A2", "รหัสบัญชี");
                    dic.Add("B2", "รหัสบัญชี GFMIS");
                    dic.Add("C2", strAccountGroup == "4" ? "รายการ รายได้" : "รายการ รายจ่าย");
                    dic.Add("D2", "จำนวนเงิน");
                }

                dic.Add("A" + nRow.ToString(), strAccountCode);
                dic.Add("B" + nRow.ToString(), strAccountCodeGFMIS);
                dic.Add("C" + nRow.ToString(), strAccountName);
                dic.Add("D" + nRow.ToString(), dAmount.ToString("#,##0.00"));

                if (strAccountGroup == "4")
                {
                    dSumIncome += dAmount;
                }
                else
                {
                    dSumOutcome += dAmount;
                }

                nRow++;

                if (i == dtb.Rows.Count - 1 || dtb.Rows[i + 1]["account_group"].ToString() != strAccountGroup)
                {
                    dic.Add("C" + nRow.ToString(), "รวม" + (strAccountGroup == "4" ? "รายได้ทั้งสิ้น" : "รายจ่ายทั้งสิ้น"));

                    if (strAccountGroup == "4")
                    {
                        dic.Add("D" + nRow.ToString(), dSumIncome.ToString("#,##0.00"));
                    }
                    else
                    {
                        dic.Add("D" + nRow.ToString(), dSumOutcome.ToString("#,##0.00"));
                    }

                    nRow++;
                    nRow++;

                    if (i != dtb.Rows.Count - 1)
                    {
                        dic.Add("A" + nRow.ToString(), "รหัสบัญชี");
                        dic.Add("B" + nRow.ToString(), "รหัสบัญชี GFMIS");
                        dic.Add("C" + nRow.ToString(), "รายการ รายจ่าย");
                        dic.Add("D" + nRow.ToString(), "จำนวนเงิน");
                        nRow++;
                    }
                    else
                    {
                        dic.Add("C" + nRow.ToString(), "รายได้สูง(ต่ำ)กว่าค่าใช้จ่าย");
                        var dSum = dSumIncome - dSumOutcome;

                        dic.Add("D" + nRow.ToString(), dSum >= 0 ? dSum.ToString("#,##0.00") : (-dSum).ToString("(#,##0.00)"));
                    }
                }
            }

            mailMerge.MergeExcel(Response, Server.MapPath("~/Reports/Templates/งบกำไรขาดทุน.xlsx"), string.Format("งบกำไรขาดทุน-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), null, dic);
        }

        private void TrialBalance(string strBgmSourceSeq, DateTime dtStartDate, DateTime dtEndDate)
        {
            DataTable dtb = lg.ReportTrialBalance(strBgmSourceSeq, dtStartDate, dtEndDate, chkPost.Checked, chkIncludeClosePeriodAdjust.Checked);
            Dictionary<string, string> dic = new Dictionary<string, string>();

            int nRow = 4;
            double dSumDrPrev = 0;
            double dSumCrPrev = 0;
            double dSumDr = 0;
            double dSumCr = 0;
            double dSumDrForw = 0;
            double dSumCrForw = 0;

            switch (rblCriteria.SelectedValue)
            {
                case "date":
                    dic.Add("A1", "ตั้งแต่วันที่ " + dtStartDate.ToLongDateString() + " ถึงวันที่ " + dtEndDate.ToLongDateString() + " " + (chkIncludeClosePeriodAdjust.Checked ? "" : "(ก่อนปิดงวด)"));
                    break;
                case "year":
                    dic.Add("A1", "ประจำปี " + (dtEndDate.Year + 543).ToString() + " " + (chkIncludeClosePeriodAdjust.Checked ? "" : "(ก่อนปิดงวด)"));
                    break;
                case "quarter":
                    if (rblQuarter.SelectedValue == "4")
                    {
                        dic.Add("A1", "ตั้งแต่วันที่ " + dtStartDate.ToLongDateString() + " ถึงวันที่ " + dtEndDate.ToLongDateString() + " " + (chkIncludeClosePeriodAdjust.Checked ? "" : "(ก่อนปิดงวด)"));
                    }
                    break;
            }

            for (int i = 0; i < dtb.Rows.Count; i++)
            {
                DataRow dr = dtb.Rows[i];

                string strAccountCode = dr["account_code"].ToString();
                string strAccountName = dr["account_name"].ToString();
                double dDrPrev = (!string.IsNullOrEmpty(dr["dr_prev"].ToString()) ? Convert.ToDouble(dr["dr_prev"].ToString()) : 0);
                double dCrPrev = (!string.IsNullOrEmpty(dr["cr_prev"].ToString()) ? Convert.ToDouble(dr["cr_prev"].ToString()) : 0);
                double dDr = (!string.IsNullOrEmpty(dr["dr"].ToString()) ? Convert.ToDouble(dr["dr"].ToString()) : 0);
                double dCr = (!string.IsNullOrEmpty(dr["cr"].ToString()) ? Convert.ToDouble(dr["cr"].ToString()) : 0);
                double dDrForw = (!string.IsNullOrEmpty(dr["dr_forw"].ToString()) ? Convert.ToDouble(dr["dr_forw"].ToString()) : 0);
                double dCrForw = (!string.IsNullOrEmpty(dr["cr_forw"].ToString()) ? Convert.ToDouble(dr["cr_forw"].ToString()) : 0);

                if (dDrPrev > 0 || dCrPrev > 0 || dDr > 0 || dCr > 0)
                {
                    dic.Add("A" + nRow.ToString(), strAccountCode);
                    dic.Add("B" + nRow.ToString(), strAccountName);
                    dic.Add("C" + nRow.ToString(), dDrPrev.ToString("#,##0.00"));
                    dic.Add("D" + nRow.ToString(), dCrPrev.ToString("#,##0.00"));
                    dic.Add("E" + nRow.ToString(), dDr.ToString("#,##0.00"));
                    dic.Add("F" + nRow.ToString(), dCr.ToString("#,##0.00"));
                    dic.Add("G" + nRow.ToString(), dDrForw.ToString("#,##0.00"));
                    dic.Add("H" + nRow.ToString(), dCrForw.ToString("#,##0.00"));
                
                    dSumDrPrev += dDrPrev;
                    dSumCrPrev += dCrPrev;
                    dSumDr += dDr;
                    dSumCr += dCr;
                    dSumDrForw += dDrForw;
                    dSumCrForw += dCrForw;

                    nRow++;
                }
            }

            dic.Add("B" + nRow.ToString(), "ยอดรวม");
            dic.Add("C" + nRow.ToString(), dSumDrPrev.ToString("#,##0.00"));
            dic.Add("D" + nRow.ToString(), dSumCrPrev.ToString("#,##0.00"));
            dic.Add("E" + nRow.ToString(), dSumDr.ToString("#,##0.00"));
            dic.Add("F" + nRow.ToString(), dSumCr.ToString("#,##0.00"));
            dic.Add("G" + nRow.ToString(), dSumDrForw.ToString("#,##0.00"));
            dic.Add("H" + nRow.ToString(), dSumCrForw.ToString("#,##0.00"));

            mailMerge.MergeExcel(Response, Server.MapPath("~/Reports/Templates/งบทดลอง.xlsx"), string.Format("งบทดลอง-{0}", DateTime.Now.ToString("yyyyMMddHHmmssffff")), null, dic);
        }
        #endregion
    }
}
